# conding=utf-8
'''
Pyqt5 文件对话框实例
'''
import tarfile

from PyQt5.QtCore import QTimer, QByteArray
from PyQt5.QtWidgets import QWidget, QApplication, QPushButton, QColorDialog, QFontDialog, QTextEdit, QFileDialog, QMessageBox
from PyQt5.QtGui import QPalette, QColor
from PyQt5.QtCore import QSettings
from configparser import NoOptionError
import configparser
import sys
import os
import shutil
import zipfile
from xml.etree import ElementTree as ET
import subprocess
from xml.dom.minidom import parse


class RemoveProtection(QWidget):
    def __init__(self):
        super().__init__()

        self.initUI()

    def initUI(self):

        # 设置主窗体的背景色（这里假设为白色，但你可以设置为任何颜色）
        palette = self.palette()
        palette.setColor(QPalette.Background, QColor(245, 245, 245))  # 设置为白色
        self.setGeometry(300, 300, 500, 300)
        self.setWindowTitle('Excel文件解密')
        self.setPalette(palette)

        self.run_file_path = os.path.join(os.getcwd(),'RemoveProtection')
        self.createDir(self.run_file_path)
        configFileName = 'RemoveProtection.ini'
        self.config_path = os.path.join(self.run_file_path,configFileName)

        self.initSettings()
        self.init_geometry_settings()

        self.init_config()

        self.tx = QTextEdit(self)
        self.tx.setGeometry(20, 20, 300, 50)

        self.bt1 = QPushButton('选择文件', self)
        self.bt1.move(350, 20)

        self.bt2 = QPushButton('解密文件', self)
        self.bt2.move(350, 60)

        self.desc = QTextEdit(self)
        self.desc.setGeometry(20, 100, 500, 200)
        self.desc.setReadOnly(False)
        self.desc.setText("PS:\n"
                          "1、功能：移除工作表保护、移除工作簿保护。\n"
                          "2、excel文件不要存在文件占用（例如：excel打开）。\n"
                          "3、文件自动备份，防止文件损坏。\n"
                          "4、日志记录:./RemoveProtection/log_info/log.txt\n\n"
                          "参数配置:\n"
                          "1、配置文件位于RemoveProtection文件夹下RemoveProtection.ini。\n"
                          "2、open_path: 默认打开的文件路径；例如：C:/Users/{用户名}/Downloads。\n"
                          "3、auto_open: 是否自动打开(是：1/yes/true/on,否：0/no/false/off)。\n"
                          "3、auto_backup: 是否文件备份(是：1/yes/true/on,否：0/no/false/off)。\n")
        self.desc.setPalette(palette)
        # 设置无边框样式
        self.desc.setStyleSheet("QTextEdit { border: none; background-color: rgb(245,245,245);}")

        self.bt1.clicked.connect(self.openFile)
        self.bt2.clicked.connect(self.decodeExcelCheck)

        self.clearObject()

        self.show()

    def init_config(self):
        # 创建一个配置解析器并指定一个INI文件
        config = configparser.ConfigParser()
        config.read(self.config_path)  # 如果文件不存在，不会抛出错误

        isInit = self.get_config_bykey('open_path','string')
        if isInit == '':
            config['DEFAULT'] = {'open_path': './',
                                 'auto_open': 'yes',
                                 'auto_backup': 'yes'}
            # 将配置写回到文件
            with open(self.config_path, 'w') as configfile:
                config.write(configfile)

    def get_config_bykey(self, key, type):
        # 创建一个配置解析器并指定一个INI文件
        config = configparser.ConfigParser()
        config.read(self.config_path)  # 如果文件不存在，不会抛出错误
        try:
            if type == 'boolean':
                return config.getboolean('DEFAULT', key)
            else:
                return config.get('DEFAULT', key)
        except NoOptionError as e:
            return ''


    def init_geometry_settings(self):
        # 读取配置
        self.geometry = self.settings.value("geometry", type=QByteArray)
        if self.geometry:
            self.restoreGeometry(self.geometry)
            # 写入配置
        self.closeEvent = self.onClosing

    def onClosing(self, event):
        self.settings.setValue("geometry", self.saveGeometry())
        event.accept()

    def initSettings(self):
        # 使用 QSettings 读取和写入配置
        self.settings = QSettings("jindong", "RemoveProtection")  # 组织名和应用程序名

    def openFile(self):
        openPath = self.get_config_bykey("open_path",'string')
        fname = QFileDialog.getOpenFileName(self, '打开文件', openPath, "Exel file(*.xls *.xlsx)")
        if fname[0]:
            self.full_path = os.path.abspath(fname[0])
            self.dir_path = os.path.dirname(self.full_path)
            self.tx.setText(self.full_path)
            self.file_name = os.path.basename(fname[0])
        self.logInfo("-------------------------------------------------------")
        self.logInfo("打开文件【{0}】成功".format(fname[0]))

    def decodeExcelCheck(self):
        self.logInfo("开始解密")
        if self.file_name == None:
            self.show_message_erroe("错误","请先选择文件",2)
        else:
            self.decodeExcel()

    def decodeExcel(self):
        self.logInfo("跳转文件目录{0}".format(self.dir_path))
        #改变当前工作目录
        os.chdir(self.dir_path)
        self.backupFile()
        self.logInfo("备份文件【{0}】成功".format(self.file_name))
        # 将待破解密码的excel文件后缀名改为.zip
        self.logInfo("开始解密文件【{0}】...".format(self.file_name))
        self.logInfo("开始解压文件【{0}】...改变文件后缀->zip".format(self.file_name))
        new_path = self.replace_suffix(self.full_path, 'zip')
        if new_path == '':
            self.clearObject()
        else:
            # 解压缩上一步的zip文件
            self.logInfo("开始解压文件【{0}】...解压".format(self.file_name))
            des_dir = self.un_zip(new_path)
            # 解压缩后，把后缀名再次改回.xlsx
            self.logInfo("复原文件【{0}】...改变文件后缀->xlsx".format(self.file_name))
            self.replace_suffix(new_path, 'xlsx')
            # 获取sheet.xml文件存放的路径
            self.logInfo("开始取消文件【{0}】保护...".format(self.file_name))
            xl_path = os.getcwd() + '\\' + des_dir + r'\xl'
            # 移除工作簿保护
            self.remove_workbook_protection(xl_path + r'\workbook.xml')
            dir_path = xl_path + r'\worksheets'
            # 遍历sheet.xml文件存放路径，获取所有sheet.xml文件
            file_list = self.walkFile(dir_path)
            # 遍历所有sheet.xml文件 修改 'sheetProtection'节点
            for i in file_list:
                # 移除工作表保护
                self.remove_sheet_protection(i)
            # 切换当前路径至解压后文件的文件夹
            zip_files_dir = os.getcwd() + '\\' + des_dir

            # 压缩该文件夹
            self.logInfo("开始压缩文件【{0}】...".format(self.file_name))
            zip_file_name = self.get_zip(zip_files_dir)
            # 切换当前路径至上一级
            pwd = os.path.abspath(os.path.join(os.getcwd(), ".."))
            os.chdir(pwd)
            # 将压缩文件的后缀名改为.xlsx，完成密码破解
            new_path = self.replace_suffix(os.getcwd() + '\\' + zip_file_name, 'xlsx')
            self.logInfo("生成文件【{0}】...".format(new_path))
            # 删除临时生成的文件夹
            shutil.rmtree(zip_files_dir)

            self.tx.setText("解密成功")
            self.show_message_info("成功", "解除保护成功", 2)
            self.openFilePath(new_path)
            self.clearObject()

    # 备份文件
    def backupFile(self):
        backup = self.get_config_bykey("auto_backup", 'boolean')
        if backup:
            # 备份文件
            self.logInfo("开始备份文件【{0}】".format(self.file_name))
            # 将指定文件复制到该文件夹
            self.createDir(self.dir_path + '\\备份')
            new_file = shutil.copy(self.file_name, self.dir_path + '\\备份' + os.sep + self.file_name)

    # 打开excel文件
    def openFilePath(self, new_path):
        autoOpen = self.get_config_bykey("auto_open",'boolean')
        if autoOpen:
            # 使用os.startfile()打开文件
            os.startfile(new_path)

    # 清理变量
    def clearObject(self):
        self.full_path = None
        self.dir_path = None
        self.tx.setText(self.full_path)
        self.file_name = None

    # 展示错误信息
    def show_message_erroe(self,title,info,seconds):
        message_box = QMessageBox(QMessageBox.Critical, title, info, QMessageBox.Ok)
        self.set_messagebox_timer(message_box, seconds)

    #展示成功信息
    def show_message_info(self,title,info,seconds):
        message_box  = QMessageBox(QMessageBox.Information, title, info, QMessageBox.Ok)
        self.set_messagebox_timer(message_box, seconds)

    # 设置弹框定时关闭
    def set_messagebox_timer(self, message_box, seconds):
        # 连接到 QTimer 的 timeout() 信号，当定时器超时时关闭消息框
        def on_timeout():
            message_box.close()

        if seconds > 0:
            # 创建一个 QTimer 实例
            timer = QTimer()
            timer.setSingleShot(True)  # 设置定时器为单次触发
            timer.timeout.connect(on_timeout)
            # 启动定时器，指定延迟时间（以毫秒为单位）
            timer.start(seconds * 1000)

            # 显示消息框并进入事件循环
            message_box.exec_()

    # 创建文件夹
    def createDir(self,path):
        exists = os.path.exists(path)
        if exists == False:
            os.makedirs(path)

    # 解压
    def un_zip(self,file_path):
        f = zipfile.ZipFile(file_path, 'r')
        (path, filename) = os.path.split(file_path)
        des_dir = str(filename).split('.')[0]
        for file in f.namelist():
            f.extract(file, des_dir)
        return des_dir

    # 压缩文件
    def get_zip(self,files_dir):
        zip_file_name = files_dir.split('\\')[-1] + '-removed_password.zip'
        f = zipfile.ZipFile(zip_file_name, 'w', zipfile.ZIP_DEFLATED)
        startdir = files_dir
        os.chdir(startdir)
        for dirpath, dirnames, filenames in os.walk(".", topdown=False):
            for filename in filenames:
                f.write(os.path.join(dirpath, filename))
        f.close()
        return zip_file_name

    # 将每个sheet.xml中'sheetProtection' 替换成其他无意义内容，如'qwe'
    def remove_sheet_protection(self,file_path):
        fileForReading = open(file_path, 'r', encoding='utf-8')
        xmlString = fileForReading.read()
        fileForReading.close()
        xmlString = xmlString.replace('sheetProtection', 'qwe')
        fileForWriting = open(file_path, 'w', encoding='utf-8')
        fileForWriting.write(xmlString)
        fileForWriting.close()

    # 将workbook.xml中'workbookProtection' 替换成其他无意义内容，如'qwe'
    def remove_workbook_protection(self, file_path):
        fileForReading = open(file_path, 'r', encoding='utf-8')
        xmlString = fileForReading.read()
        fileForReading.close()
        xmlString = xmlString.replace('workbookProtection', 'qwe')
        fileForWriting = open(file_path, 'w', encoding='utf-8')
        fileForWriting.write(xmlString)
        fileForWriting.close()

    # 用于更改文件后缀名
    def replace_suffix(self,file_path, suffix):
        new_path = ''
        for i in range(1, len(file_path)):
            if file_path[-i] == '.':  # 找到后缀初始点
                new_path = file_path.replace(file_path[-i + 1:], suffix)  # 替换后缀
                break
        try:
            os.rename(file_path, new_path)
        except OSError as e:
            new_path = ''
            self.logInfo("文件存在占用，请先关闭占用")
            self.show_message_erroe("TIPS", "文件存在占用，请先关闭占用",2)
        return new_path

    # 用于遍历文件夹，获取所有xml文件
    def walkFile(self,rootDir):
        list_dirs = os.walk(rootDir)
        file_list = []
        for root, dirs, files in list_dirs:
            for f in files:
                if f.split('.')[-1] == 'xml':
                    file_list.append(os.path.join(root, f))
        return file_list

    # 用于遍历文件夹，获取所有xml文件
    def logInfo(self, message):
        path = os.path.join(self.run_file_path,'log_info')
        self.createDir(path)
        log_file_path = os.path.join(path,'log.txt')
        # 创建文件
        try:
            with open(log_file_path,'a') as file:
                file.write(message + '\n')
        except FileExistsError:
            print("文件已存在，无法创建。")



if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = RemoveProtection()
    sys.exit(app.exec_())